package com.dao.Impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.dao.BaseDao;
import com.dao.SingerDao;
import com.dao.SingerTypeDao;
import com.entity.Singer;
import com.entity.SingerType;
import com.entity.Data.PagerData;
import com.entity.Query.SingerQuery;
import com.util.MyUtil;

@Repository
public class SingerDaoImpl extends BaseDao implements SingerDao{

	@Override
	public List<Map<String, Object>> querySinger() {
		String sql ="select s.singerId,s.singerName,s.seName,s.state,s.address,"
				+ "s.`work`,s.birthday,s.singerPhoto,st.singerTypeName from singer as s "
				+ "join singertype st on s.singerTypeId=st.singerTypeId";
		return executeQuery(sql, null);
	}

	@Override
	public Map<String, Object> querySingerId(int singerId) {
		String sql ="select s.singerId,s.singerName,s.seName,s.state,s.address,"
				+ "s.`work`,s.birthday,s.singerPhoto,s.singerTypeId,st.singerTypeName from singer as s "
				+ "join singertype st on s.singerTypeId=st.singerTypeId where singerId=?";
		Object[] params={singerId};
		return executeQueryMap(sql, params);
	}

	@Override
	public int addSinger(Singer singer) {
		String sql ="insert into singer(singerName,seName,state,address,"
				+ "work,birthday,singerPhoto,singerTypeId)values(?,?,?,?,?,?,?,?)";
		Object[] params={singer.getSingerName(),singer.getSeName(),singer.getState(),
				singer.getAddress(),singer.getWork(),singer.getBirthday(),singer.getSingerPhoto(),
				singer.getSingerTypeId()};
		return executeUpdate(sql, params);
	}

	@Override
	public int editSinger(Singer singer) {
		String sql ="update singer	set	singerName=?,"
				+ "seName=?,state=?,address=?,work=?,"
				+ "birthday=?,"
				+ "singerTypeId=?	where singerId=?";
		Object[] params={singer.getSingerName(),singer.getSeName(),singer.getState(),
				singer.getAddress(),singer.getWork(),singer.getBirthday(),
				singer.getSingerTypeId(),singer.getSingerId()};
		return executeUpdate(sql, params);
	}

	@Override
	public int delSinger(int singerId) {
		String sql ="delete from singer where singerId=?";
		Object[] params={singerId};
		return executeUpdate(sql, params);
	}

	@Override
	public PagerData queryByPage(SingerQuery singerQuery, int curPage, int pageSize) {
		//根据条件拼接where语句
		String sqlWhere= "";
		List<Object> params = new ArrayList<Object>();
		
		//姓名条件不能为空
		if (!MyUtil.isNull(singerQuery.getSingerName())) {
			sqlWhere +=" and s.singerName like ?";
			params.add(singerQuery.getSingerName());
		}
		
		//国籍条件不能为空
		if (!MyUtil.isNull(singerQuery.getState())) {
			sqlWhere +=" and s.state like ?";
			params.add(singerQuery.getState());
		}
		
		//出生地条件不能为空
		if (!MyUtil.isNull(singerQuery.getAddress())) {
			sqlWhere +=" and s.address like ?";
			params.add(singerQuery.getAddress());
		}
		
		//职业条件不能为空
		if (!MyUtil.isNull(singerQuery.getWork())) {
			sqlWhere +=" and s.`work` like ?";
			params.add("%"+singerQuery.getWork()+"%");
		}
		
		//起始日期
		if(singerQuery.getStartDate()!=null){
			sqlWhere +=" and s.birthday >= ? ";
			params.add(singerQuery.getStartDate());
		}
		
		//结束日期
		if(singerQuery.getEndDate()!=null){
			sqlWhere +=" and s.birthday <= ? ";
			params.add(singerQuery.getEndDate());
		}
		
		//歌手类型
		if(singerQuery.getSingerTypeId()!=null){
			sqlWhere += " and st.singerTypeId = ? ";
			params.add(singerQuery.getSingerTypeId());
		}
		
		//获取带条件查询的总记录数
		String sqlTotal = " select count(*) from singer as s "
				+ " join singerType as st "
				+ " on s.singerTypeId=st.singerTypeId "
				+ " where 1=1"+sqlWhere;
		
		int totalCount = jdbcTemplate.queryForInt(sqlTotal,params.toArray());
		int totalPage = totalCount%pageSize==0 ?
						totalCount/pageSize :
						totalCount/pageSize+1;
		
		String sql =" select s.singerId,s.singerName,s.seName,"
				+ " s.state,s.address,s.`work`,s.birthday,"
				+ " s.singerPhoto,st.singerTypeId,st.singerTypeName "
				+ " from singer as s  join singerType as st  "
				+ " on s.singerTypeId=st.singerTypeId where 1=1"+sqlWhere
				+ " order by s.singerId"
				+ " limit ?,?";
		//计算分页查询的开始位置
		int start = (curPage-1)*pageSize;
		params.add(start);
		params.add(pageSize);
		
		List<Map<String, Object>> singerList = executeQuery(sql, params.toArray());
		//封装分页数据对象
		PagerData pd = new PagerData();
		//当前第几页
		pd.setCurPage(curPage);
		//每页几条
		pd.setPageSize(pageSize);
		//总记录数
		pd.setTotalCount(totalCount);
		//总页数
		pd.setTotalPage(totalPage);
		//当前页数据记录
		pd.setDataList(singerList);
		return pd;
	}

}
